{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: mysql in f:\\python\\lib\\site-packages (0.0.3)\n",
      "Requirement already satisfied: mysqlclient in f:\\python\\lib\\site-packages (from mysql) (2.2.7)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "[notice] A new release of pip available: 22.3.1 -> 24.3.1\n",
      "[notice] To update, run: python.exe -m pip install --upgrade pip\n"
     ]
    }
   ],
   "source": [
    "pip install mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "import MySQLdb as mdb\n",
    "\n",
    "import matplotlib\n",
    "matplotlib.use(\"TkAgg\")\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from matplotlib.collections import LineCollection\n",
    "\n",
    "import pandas as pd\n",
    "from sklearn import cluster,covariance,manifold\n",
    "\n",
    "from matplotlib.font_manager import FontProperties\n",
    "\n",
    "\n",
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api('579f438eb79d5ee41e68586e4adf0dfc11edb58d68b6843e9d843518')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5393 entries, 0 to 5392\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   ts_code       5393 non-null   object\n",
      " 1   symbol        5393 non-null   object\n",
      " 2   name          5393 non-null   object\n",
      " 3   area          5378 non-null   object\n",
      " 4   industry      5378 non-null   object\n",
      " 5   cnspell       5393 non-null   object\n",
      " 6   market        5393 non-null   object\n",
      " 7   list_date     5393 non-null   object\n",
      " 8   act_name      2574 non-null   object\n",
      " 9   act_ent_type  2574 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 421.5+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ts_code         5393\n",
       "symbol          5393\n",
       "name            5393\n",
       "area            5378\n",
       "industry        5378\n",
       "cnspell         5393\n",
       "market          5393\n",
       "list_date       5393\n",
       "act_name        2574\n",
       "act_ent_type    2574\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pro.query('stock_basic', exchange='',list_status='L')\n",
    "data.info()\n",
    "data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5393"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:lijingqi@localhost/stock_basic')\n",
    "\n",
    "data.to_sql('stock_basic', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 000001.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000001.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 000002.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000002.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 000004.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000004.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 000006.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000006.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 000007.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000007.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 000008.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000008.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7 000009.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000009.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8 000010.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000010.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9 000011.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000011.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 000012.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000012.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11 000014.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000014.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12 000016.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000016.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 000017.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000017.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14 000019.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000019.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "15 000020.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000020.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "16 000021.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000021.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "17 000025.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000025.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "18 000026.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000026.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19 000027.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000027.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20 000028.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000028.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21 000029.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000029.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22 000030.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000030.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23 000031.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000031.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "24 000032.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000032.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "25 000034.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000034.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26 000035.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000035.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "27 000036.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000036.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "28 000037.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000037.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "29 000039.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000039.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30 000040.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000040.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "31 000042.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000042.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32 000045.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000045.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "33 000048.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000048.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "34 000049.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000049.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35 000050.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000050.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "36 000055.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\www\\AppData\\Local\\Temp\\ipykernel_13928\\2707167430.py:12: UserWarning: The provided table name '000055.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock, con=engine, if_exists='append', index=False)\n"
     ]
    }
   ],
   "source": [
    "stock_list = data['ts_code'].tolist()\n",
    "\n",
    "data_price=[]\n",
    "\n",
    "i=0\n",
    "for stock in data['ts_code'].tolist():\n",
    "            if i==36:\n",
    "                    break\n",
    "            i+=1\n",
    "            print(i,stock)\n",
    "            data_price=pro.daily(ts_code=stock)\n",
    "            data_price.to_sql(stock, con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   count(*)\n",
      "0      5393\n",
      "     ts_code  symbol  name area industry cnspell market list_date act_name  \\\n",
      "0  000009.SZ  000009  中国宝安   深圳     电气设备    zgba     主板  19910625   无实际控制人   \n",
      "\n",
      "  act_ent_type  \n",
      "0            无  \n"
     ]
    }
   ],
   "source": [
    "sql = \"select count(*) from stock_basic.stock_basic\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)\n",
    "\n",
    "sql = \"select * from stock_basic.stock_basic where name='中国宝安'\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         ts_code trade_date  open  high   low  close  pre_close  change  \\\n",
      "0      000009.SZ   20250123  8.65  8.77  8.52   8.53       8.56   -0.03   \n",
      "1      000009.SZ   20250122  8.64  8.64  8.46   8.56       8.64   -0.08   \n",
      "2      000009.SZ   20250121  8.80  8.83  8.60   8.64       8.72   -0.08   \n",
      "3      000009.SZ   20250120  8.71  8.85  8.67   8.72       8.66    0.06   \n",
      "4      000009.SZ   20250117  8.62  8.69  8.58   8.66       8.65    0.01   \n",
      "...          ...        ...   ...   ...   ...    ...        ...     ...   \n",
      "17995  000009.SZ   19990622  4.02  4.10  3.95   4.10       4.00    0.10   \n",
      "17996  000009.SZ   19990621  3.90  4.03  3.83   4.00       3.90    0.10   \n",
      "17997  000009.SZ   19990618  3.96  3.97  3.79   3.90       3.99   -0.09   \n",
      "17998  000009.SZ   19990617  4.12  4.15  3.92   3.99       4.12   -0.13   \n",
      "17999  000009.SZ   19990616  4.02  4.12  3.89   4.12       4.03    0.09   \n",
      "\n",
      "       pct_chg        vol       amount  \n",
      "0      -0.3505  129813.41  112114.1810  \n",
      "1      -0.9259  103666.08   88498.8640  \n",
      "2      -0.9174   85851.74   74322.3370  \n",
      "3       0.6928  107580.98   93976.1900  \n",
      "4       0.1156   88403.44   76362.5000  \n",
      "...        ...        ...          ...  \n",
      "17995   2.5000  114696.00   46251.6705  \n",
      "17996   2.5600  120029.00   47449.0672  \n",
      "17997  -2.2600  111478.00   42878.8067  \n",
      "17998  -3.1600  124205.00   49311.2962  \n",
      "17999   2.2300   49441.00   19722.6378  \n",
      "\n",
      "[18000 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "sql = \"select * from stock_basic.`000009.sz`\"\n",
    "df = pd.read_sql(sql, engine)\n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
